package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;


import ModelLayer.Product;

public class DBProduct {
	private static Connection con;
	Statement stmt;
	public DBProduct() 
	{
		con = DbConnection.getInstance().getDBcon();
	}
	public void createProduct(String name,String price,String ocountry,int stock ,int suID,int clID,int eqID,int gID,int lID,boolean isLoan)
	{	String query="";
		try{
			int pID=GetMax.getMaxId("Select max(pID) from Product")+1;//Gets the maximum pID from the DB and adds+1 for the next ID
			if(isLoan)
			{ query ="INSERT INTO Product(pID,name, price, origin_country, stock, suID, clID, eqID, gID, lID)  VALUES('"+pID+"','"+name+"','"+price+"','"+ocountry+"','"+stock+"','"+suID+"','"+clID+"','"+eqID+"','"+gID+"','"+lID+"')";}
			else
			{ 	int a=0;//Not loan ID in the DB
				query ="INSERT INTO Product( pID, name, price, origin_country, stock, suID, clID, eqID, gID, lID)  VALUES('"+pID+"','"+name+"','"+price+"','"+ocountry+"','"+stock+"','"+suID+"','"+clID+"','"+eqID+"','"+gID+"','"+a+"')";}
			 Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{	System.out.println("SuID,clID or gID is most probably invalid!");
			e.printStackTrace();
			}
	}
	public void deleteProduct(int pID)
	{	//pID=1; 
		try{
			String query="DELETE FROM Product WHERE pID = '"+pID+"'";
			Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{e.printStackTrace();
		}
	}
	public void updateProduct(Product g)
	{ Product suObj = g;
	
	
	 String query="UPDATE Product SET "+
			  "pID ='"+ suObj.getpID()+"', "+
			  "name ='"+ suObj.getName()+"', "+
		 	  "price ='"+ suObj.getPrice() + "'" +
		 	 "origin_country ='"+ suObj.getOcountry() + "'" +
		 	"stock ='"+ suObj.getStock() + "'" +
		 	"suID ='"+ suObj.getSuID() + "'" +
		 	"clID ='"+ suObj.getClID() + "'" +
		 	"eqID ='"+ suObj.getEqID() + "'" +
		 	"gID ='"+ suObj.getgID() + "'" +
		 	"lID ='"+ suObj.getlID() + "'" +
		          " WHERE pID = '"+ suObj.getpID() + "'";
	
              System.out.println("Update query:" + query);
              try{
            	  Statement stmt = con.createStatement();
            	  stmt.setQueryTimeout(5);
            	  stmt.executeUpdate(query);
            	  stmt.close();
            	  
              }
              catch(Exception e)
              {e.printStackTrace();}
		
	}
	
	private String buildQuery()
	{
	    String query="SELECT pID,name,price,origin_country,stock,suID,clID,eqID,gID,lID  FROM Product";
		/*
		if (wClause.length()>0)
			query=query+" WHERE ";
			*/
		return query;
	}
	public ArrayList<Product> miscWhere()
	{
            ResultSet results;
	    ArrayList<Product> list = new ArrayList<Product>();	
		
	    String query =  buildQuery();
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Product empObj = new Product();
		 empObj = buildProduct(results);	
                 list.add(empObj);	
		}//end while
                 stmt.close();     
                 
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	public Product findProduct(int pID)
	{
		ResultSet results;
		Product empObj = new Product();
                
	        String query =  buildQuery() + " WHERE pID ="+"'"+pID+"'";
                System.out.println(query);
		try{ // 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            empObj = buildProduct(results);
                            //assocaition is to be build
                            stmt.close();
                            
                            
			}
                        else{ //no Product was found
                            empObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return empObj;
	}
	private Product buildProduct(ResultSet results)
    {   Product suObj = new Product();
        try{ // the columns from the table glayee  are used
              suObj.setpID(results.getInt("pID"));
              suObj.setName(results.getString("name"));
              suObj.setPrice(results.getString("price"));
              suObj.setOcountry(results.getString("origin_country"));
              suObj.setStock(results.getInt("stock"));
              suObj.setSuID(results.getInt("suID"));
              suObj.setClID(results.getInt("clID"));
              suObj.setEqID(results.getInt("eqID"));
              suObj.setgID(results.getInt("gID"));
              suObj.setlID(results.getInt("lID"));
              
        }
       catch(Exception e)
       {
           System.out.println("Error in building the Product object");
       }
       return suObj;
}
	
}